﻿using System;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Text;
using System.Text.RegularExpressions;

namespace NutriStat.Data
{
    public class SqlDatabase : NutriStat.Data.DbDriverBase
    {

        #region Constructor
        
        public SqlDatabase()
        {
        }
        #endregion

        #region Native Driver Implementation
        
        protected virtual SqlParameter ConvertToNativeParameter(QueryParameter parameter)
        {
            if (parameter.DbType.Equals(DbType.Guid))
            {
                parameter.Value = new Guid(parameter.Value.ToString());
            }

            return new SqlParameter(parameter.ParameterName, CovertToNativeDbType(parameter.DbType), parameter.Size, parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.Value);
        }

        
        protected virtual SqlConnection GetNativeConnection()
        {
            return GetNativeConnection(connectionString);
        }

        
        protected virtual SqlConnection GetNativeConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        
        protected SqlCommand GetNativeCommand(IDbTransaction transaction)
        {
            SqlTransaction oleDbtransaction = transaction as SqlTransaction;

            #region Input Validation
            if (oleDbtransaction == null)
            {
                throw new ArgumentException("Transaction parameter must be a SqlTransaction.", "transaction");
            }
            #endregion

            return new SqlCommand(null, (SqlConnection)transaction.Connection, (SqlTransaction)transaction);

        }
        
        public override DataTable Select(Query selectQuery, DataTable dataTable)
        {
            #region Input Validation
            if (selectQuery == null)
            {
                throw new ArgumentNullException("selectQuery");
            }
            if (dataTable == null)
            {
                throw new ArgumentNullException("dataTable");
            }
            #endregion Input Validation


            IDbConnection connection = GetConnection();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = (SqlCommand)GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters);
            adapter.SelectCommand.CommandTimeout = 1500;

            try
            {
                adapter.Fill(dataTable);
                return dataTable;
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error executing select query against the database.", ex);
            }
        }
        
        public override void Update(DataTable dataTable, string tableName, Query insertQuery, Query updateQuery)
        {

            #region Input Validation

            if (dataTable == null)
            {
                throw new ArgumentNullException("DataTable");
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("TableName");
            }
            if (insertQuery == null)
            {
                throw new ArgumentNullException("InsertQuery");
            }
            if (updateQuery == null)
            {
                throw new ArgumentNullException("UpdateQuery");
            }
            #endregion Input Validation

            IDbConnection connection = GetConnection();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.InsertCommand = (SqlCommand)GetCommand(insertQuery.SqlStatement, connection, insertQuery.Parameters);
            adapter.UpdateCommand = (SqlCommand)GetCommand(updateQuery.SqlStatement, connection, updateQuery.Parameters);
            adapter.InsertCommand.CommandTimeout = 1500;
            adapter.UpdateCommand.CommandTimeout = 1500;
            //Logger.Log(insertQuery);
            //Logger.Log(updateQuery);
            try
            {
                adapter.Update(dataTable);
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error updating data.", ex);
            }
        }

        #endregion

        #region Temporary junk pile        
        public override string DbName // Implements IDbDriver.DbName
        {
            get
            {
                return dbName;
            }
            set
            {
                dbName = value;
            }
        }  private string dbName = string.Empty;
        
        public override string FullName //Implements DataSource.FullName
        {
            get
            {
                return DataSource + "." + DbName;
            }
        }

        private string connectionString;
        
        public override string GetDbSpecificColumnType(GenericDbColumnType dataType)
        {
            switch (dataType)
            {
                case GenericDbColumnType.AnsiString:
                case GenericDbColumnType.String:
                    return "nvarchar";

                case GenericDbColumnType.AnsiStringFixedLength:
                case GenericDbColumnType.StringFixedLength:
                    return "nchar";

                case GenericDbColumnType.Binary:
                    return "binary";

                case GenericDbColumnType.Boolean:
                    return "bit";

                case GenericDbColumnType.Byte:
                    return "tinyint";

                case GenericDbColumnType.Currency:
                    return "money";

                case GenericDbColumnType.Date:
                    return "smalldatetime";

                case GenericDbColumnType.DateTime:
                case GenericDbColumnType.Time:
                    return "datetime";

                case GenericDbColumnType.Decimal:
                    return "decimal";

                case GenericDbColumnType.Double:
                    return "float";

                case GenericDbColumnType.Guid:
                    return "uniqueidentifier";

                case GenericDbColumnType.Image:
                    return "image";

                case GenericDbColumnType.Int16:
                case GenericDbColumnType.UInt16:
                    return "smallint";

                case GenericDbColumnType.Int32:
                case GenericDbColumnType.UInt32:
                    return "int";

                case GenericDbColumnType.Int64:
                case GenericDbColumnType.UInt64:
                    return "bigint";

                case GenericDbColumnType.Object:
                    return "blob";

                case GenericDbColumnType.SByte:
                    return "tinyint";

                case GenericDbColumnType.Single:
                    return "real";

                case GenericDbColumnType.StringLong:
                    return "ntext";

                case GenericDbColumnType.VarNumeric:
                    return "decimal";

                case GenericDbColumnType.Xml:
                    return "xml";

                default:
                    throw new Exception("genericDbColumnType is unknown");
                // return "nvarchar";
            }
        }
        
        public override string ConnectionDescription
        {
            get { return "Microsoft SQL Server: " + this.DbName; }
        }
        
        public override string DataSource
        {
            get
            {
                SqlConnection sqlconn = GetConnection() as SqlConnection;
                if (sqlconn != null)
                {
                    return sqlconn.DataSource;
                }
                else
                {
                    return null;
                }
            }
        }
        
        public override string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                this.connectionString = value;

                try
                {
                    IDbConnection conn = GetConnection();
                    this.DbName = conn.Database;
                }
                catch
                {
                    this.connectionString = null;
                }
            }
        }
        
        public override string OleConnectionString
        {
            get
            {
                if (!string.IsNullOrEmpty(connectionString))
                {
                    return "Provider=sqloledb;" + connectionString;
                }
                else
                {
                    return null;
                }
            }
        }
        
        public override List<string> GetTableNames()
        {
            /*
            List<string> tableNames = new List<string>();
            DataTable schemaTable = GetTableSchema();

            foreach (DataRow row in schemaTable.Rows)
            {
                tableNames.Add(row[ColumnNames.SCHEMA_TABLE_NAME].ToString());
            }
            return tableNames;
            */
            


            
            List<string> tableNames = new List<string>();

            string connectionString = this.ConnectionString;
            DataTable tables = new DataTable("Tables");
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select table_name as Name from              INFORMATION_SCHEMA.Tables where TABLE_TYPE =              'BASE TABLE'";
                connection.Open();
                tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
            }

            for (int i = 0; i < tables.Rows.Count; i++)
            {
                tableNames.Add(tables.Rows[i]["Name"].ToString());
            }

            /*
            DbConnection connection = this.GetNativeConnection();
            try
            {
                OpenConnection(connection);
                
                DataTable tables = null;

                string[] restrictions = new string[4];
                restrictions[3] = "Table";
                tables = connection.GetSchema("Tables", restrictions);

                for (int i = 0; i < tables.Rows.Count; i++)
                {
                    tableNames.Add(tables.Rows[i][2].ToString());
                }
            }
            finally
            {
                CloseConnection(connection);
            }
            */
            return tableNames;
        }
        
        private SqlDbType CovertToNativeDbType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.AnsiString:
                    return SqlDbType.VarChar;
                case DbType.AnsiStringFixedLength:
                    return SqlDbType.Char;
                case DbType.Binary:
                    return SqlDbType.Binary;
                case DbType.Boolean:
                    return SqlDbType.Bit;
                case DbType.Byte:
                    return SqlDbType.TinyInt;
                case DbType.Currency:
                    return SqlDbType.Money;
                case DbType.Date:
                    return SqlDbType.DateTime;
                case DbType.DateTime:
                    return SqlDbType.DateTime;
                case DbType.Decimal:
                    return SqlDbType.Decimal;
                case DbType.Double:
                    return SqlDbType.Float;
                case DbType.Guid:
                    return SqlDbType.UniqueIdentifier;
                case DbType.Int16:
                    return SqlDbType.SmallInt;
                case DbType.Int32:
                    return SqlDbType.Int;
                case DbType.Int64:
                    return SqlDbType.BigInt;
                case DbType.Object:
                    return SqlDbType.VarChar;
                case DbType.SByte:
                    return SqlDbType.TinyInt;
                case DbType.Single:
                    return SqlDbType.Real;
                case DbType.String:
                    return SqlDbType.NVarChar;
                case DbType.StringFixedLength:
                    return SqlDbType.NChar;
                case DbType.Time:
                    return SqlDbType.DateTime;
                case DbType.UInt16:
                    return SqlDbType.SmallInt;
                case DbType.UInt32:
                    return SqlDbType.Int;
                case DbType.UInt64:
                    return SqlDbType.BigInt;
                case DbType.VarNumeric:
                    return SqlDbType.Decimal;
                default:
                    return SqlDbType.VarChar;
            }
        }
        #endregion

        #region Schema and DDL Support
        
        public override int GetTableCount()
        {
            /*
            DataTable dtSchema = GetTableSchema();
            return dtSchema.Rows.Count;
            */

            int tableCount = 0;

            DbConnection connection = this.GetNativeConnection();
            try
            {
                OpenConnection(connection);

                DataTable tables = null;

                string[] restrictions = new string[4];
                restrictions[3] = "Table";
                tables = connection.GetSchema("Tables", restrictions);

                for (int i = 0; i < tables.Rows.Count; i++)
                {
                    tableCount++;
                }
            }
            finally
            {
                CloseConnection(connection);
            }
            return tableCount;
        }


        
        public override void CreateTable(string tableName, List<TableColumn> columns)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("create table ");
            sb.Append(tableName);
            sb.Append(" ( ");
            foreach (TableColumn column in columns)
            {
                sb.Append(column.Name);
                sb.Append(" ");
                sb.Append(GetDbSpecificColumnType(column.DataType));
                if (column.Length != null)
                {
                    sb.Append("(");
                    sb.Append(column.Length.Value.ToString());
                    sb.Append(") ");
                }
                if (!column.AllowNull)
                {
                    sb.Append(" NOT ");
                }
                sb.Append(" null ");
                if (column.IsIdentity)
                {
                    sb.Append(" identity ");
                }
                if (column.IsPrimaryKey)
                {
                    sb.Append(" constraint ");
                    sb.Append(" PK_");
                    sb.Append(column.Name);
                    sb.Append("_");
                    sb.Append(tableName);
                    sb.Append(" primary key ");
                }
                if (!string.IsNullOrEmpty(column.ForeignKeyColumnName) && !string.IsNullOrEmpty(column.ForeignKeyTableName))
                {
                    sb.Append(" references ");
                    sb.Append(column.ForeignKeyTableName);
                    sb.Append("(");
                    sb.Append(column.ForeignKeyColumnName);
                    sb.Append(") ");
                    if (column.CascadeDelete)
                    {
                        sb.Append(" on delete cascade");
                    }

                }
                sb.Append(", ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(") ");

            ExecuteNonQuery(CreateQuery(sb.ToString()));

        }

        
        public override bool DeleteTable(string tableName)
        {
            Query query = this.CreateQuery("Drop Table " + tableName);
            //QueryParameter qr = new QueryParameter("@Name", DbType.String, tableName);
            return (ExecuteNonQuery(query) > 0);
        }
        
        public override bool TableExists(string tableName)
        {
            Query query = this.CreateQuery("select * from SysObjects O where ObjectProperty(O.ID,'IsUserTable')=1 and O.Name=@Name");
            query.Parameters.Add(new QueryParameter("@Name", DbType.String, tableName));
            return (Select(query).Rows.Count > 0);
        }
        
        public override bool ColumnExists(string tableName, string columnName)
        {
            Query query = this.CreateQuery("select * from SysObjects O inner join SysColumns C on O.ID=C.ID where ObjectProperty(O.ID,'IsUserTable')=1 and O.Name=@TableName and C.Name=@ColumnName");
            query.Parameters.Add(new QueryParameter("@TableName", DbType.String, tableName));
            query.Parameters.Add(new QueryParameter("@ColumnName", DbType.String, columnName));
            // TODO: Make sure this query returns only one row. If more than one columns is returned, it is an indication the query is wrong.
            return (Select(query).Rows.Count > 0);
        }
        
        public override List<string> GetTableColumnNames(string tableName)
        {            
            DataTable table = this.GetSchema("COLUMNS", tableName);
            List<string> list = new List<string>();
            foreach (DataRow row in table.Rows)
            {
                list.Add(row["COLUMN_NAME"].ToString());
            }
            return list;
        }
        
        private DataTable GetSchema(string collectionName, string tableName)
        {
            SqlConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                return conn.GetSchema(collectionName, new string[] { null, null, tableName });
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        
        private DataTable GetSchema(string collectionName)
        {
            SqlConnection conn = this.GetNativeConnection();

            try
            {
                OpenConnection(conn);
                return conn.GetSchema(collectionName, new string[] { null, null, null });
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        /*
        public override DataSets.TableSchema.TablesDataTable GetTableSchema()
        {
            SqlConnection conn = this.GetNativeConnection();
            try
            {
                OpenConnection(conn);
                DataTable table = conn.GetSchema("Tables", new string[0]);
                DataSets.TableSchema tableSchema = new Epi.DataSets.TableSchema();
                tableSchema.Merge(table);
                return tableSchema._Tables;
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        
        public override DataSets.TableKeysSchema.Primary_KeysDataTable GetTableKeysSchema(string tableName)
        {
            DataSets.TableKeysSchema schema = new Epi.DataSets.TableKeysSchema();
            SqlConnection conn = this.GetNativeConnection();
            bool alreadyOpen = (conn.State != ConnectionState.Closed);
            try
            {
                if (!alreadyOpen)
                {
                    OpenConnection(conn);
                }
                string sql = "select KU.TABLE_CATALOG, KU.TABLE_SCHEMA, KU.TABLE_NAME, COLUMN_NAME, " +
                              "0 as COLUMN_PROPID, ORDINAL_POSITION as ORDINAL, KU.CONSTRAINT_NAME as PK_NAME " +
                              "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " +
                              "inner join " +
                              "INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU " +
                              "on TC.CONSTRAINT_TYPE = 'primary key' and " +
                              "TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME " +
                              "where KU.TABLE_NAME = '" + tableName +
                              "' order by KU.ORDINAL_POSITION;";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(schema.Primary_Keys);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw new GeneralException("Unable to obtain primary keys schema.", ex);
            }
            finally
            {
                if (!alreadyOpen && conn.State != ConnectionState.Closed)
                {
                    CloseConnection(conn);
                }
            }
            return schema.Primary_Keys;
        }
        
        public override DataSets.TableColumnSchema.ColumnsDataTable GetTableColumnSchema(string tableName)
        {
            try
            {
                DataTable table = this.GetSchema("Columns", tableName);
                // IS_NULLABLE and DATA_TYPE are different data types for non OleDb DataTables.
                DataSets.TableColumnSchema tableColumnSchema = new Epi.DataSets.TableColumnSchema();
                tableColumnSchema.Merge(table, false, MissingSchemaAction.Ignore);
                return tableColumnSchema.Columns;

            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not get table column schema for " + tableName + ".", ex);
            }
        }
        
        public override DataSets.ANSI.TableColumnSchema.ColumnsDataTable GetTableColumnSchemaANSI(string tableName)
        {
            try
            {
                DataTable table = this.GetSchema("Columns", tableName);
                // IS_NULLABLE and DATA_TYPE are different data types for non OleDb DataTables.
                DataSets.ANSI.TableColumnSchema tableColumnSchema = new Epi.DataSets.ANSI.TableColumnSchema();
                tableColumnSchema.Merge(table, false, MissingSchemaAction.Ignore);
                return tableColumnSchema.Columns;
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not get table column schema for " + tableName + ".", ex);
            }
        }
        */
        public override DataView GetTextColumnNames(string tableName)
        {
            return new DataView(this.GetSchema("Columns", tableName));
            //            throw new System.ApplicationException("Table column schema can't be retrieved from SQL databases.");
        }
        
        public override Query CreateQuery(string sqlStatement)
        {
            return new SqlQuery(sqlStatement);
        }
        #endregion

        #region Generic IDbTransaction Methods (mirror Epi.Data.Office/Epi.Data.SqlServer)
        
        public override bool TestConnection()
        {
            try
            {
                return TestConnection(connectionString);
            }
            catch (Exception ex)
            {
                throw new Exception("Could not connect to data source.", ex);
            }
        }
        
        protected bool TestConnection(string connectionString)
        {

            IDbConnection testConnection = GetConnection(connectionString);
            try
            {
                OpenConnection(testConnection);
            }
            finally
            {
                CloseConnection(testConnection);
            }

            return true;

        }
        
        protected virtual IDbConnection GetConnection()
        {
            return GetNativeConnection(connectionString);
        }
        
        protected virtual IDbConnection GetConnection(string connectionString)
        {
            return GetNativeConnection(connectionString);
        }

        protected virtual IDbCommand GetCommand(string sqlStatement, IDbTransaction transaction, List<QueryParameter> parameters)
        {

            #region Input Validation
            if (string.IsNullOrEmpty(sqlStatement))
            {
                throw new ArgumentNullException("sqlStatement");
            }
            if (parameters == null)
            {
                throw new ArgumentNullException("Parameters");
            }
            #endregion

            IDbCommand command = this.GetNativeCommand(transaction);
            command.CommandText = sqlStatement;

            foreach (QueryParameter parameter in parameters)
            {
                command.Parameters.Add(this.ConvertToNativeParameter(parameter));
            }

            return command;
        }
       
        protected virtual IDbCommand GetCommand(string sqlStatement, IDbConnection connection, List<QueryParameter> parameters)
        {

            #region Input Validation
            if (string.IsNullOrEmpty(sqlStatement))
            {
                throw new ArgumentNullException("sqlStatement");
            }
            if (parameters == null)
            {
                throw new ArgumentNullException("parameters");
            }
            #endregion

            IDbCommand command = connection.CreateCommand();
            command.CommandText = sqlStatement;

            foreach (QueryParameter parameter in parameters)
            {
                command.Parameters.Add(this.ConvertToNativeParameter(parameter));
            }

            return command;
        }
       
        protected void OpenConnection(IDbConnection conn)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException("Error opening connection.", ex);
            }
        }
        
        protected void CloseConnection(IDbConnection conn)
        {
            try
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                conn = null;
                throw new System.ApplicationException("Error closing connection.", ex);
            }
        }
        
        public override void CloseTransaction(IDbTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("Transaction cannot be null.", "transaction");
            }

            CloseConnection(transaction.Connection);
        }
        
        public override IDbTransaction OpenTransaction()
        {
            IDbConnection connection = GetConnection();
            OpenConnection(connection);
            return connection.BeginTransaction();
        }
        
        public override IDbTransaction OpenTransaction(IsolationLevel isolationLevel)
        {
            IDbConnection connection = GetConnection();
            OpenConnection(connection);
            return connection.BeginTransaction(isolationLevel);
        }
        
        public override IDataReader GetTableDataReader(string tableName)
        {
            Query query = this.CreateQuery("select * from " + tableName);
            return this.ExecuteReader(query);
        }
        
        public override DataTable Select(Query selectQuery)
        {
            #region Input Validation
            if (selectQuery == null)
            {
                throw new ArgumentNullException("selectQuery");
            }
            #endregion

            DataTable table = new DataTable();
            return Select(selectQuery, table);

        }
        
        public override DataTable GetTableData(string tableName, string columnNames, string sortCriteria)
        {
            try
            {
                if (string.IsNullOrEmpty(columnNames))
                {
                    columnNames = "*";
                }
                string queryString = "select " + columnNames + " from [" + tableName + "]";
                if (!string.IsNullOrEmpty(sortCriteria))
                {
                    queryString += " order by " + sortCriteria;
                }
                Query query = this.CreateQuery(queryString);
                return Select(query);
            }
            finally
            {
            }
        }
        
        public override IDataReader ExecuteReader(Query selectQuery)
        {

            #region Input Validation
            if (selectQuery == null)
            {
                throw new ArgumentNullException("SelectQuery");
            }
            #endregion

            IDbConnection connection = GetConnection();

            try
            {
                OpenConnection(connection);
                IDbCommand command = GetCommand(selectQuery.SqlStatement, connection, selectQuery.Parameters);
                return command.ExecuteReader();
            }

            catch (Exception ex)
            {
                throw new System.ApplicationException("Could not execute reader", ex);
            }
            finally
            {

            }
        }
        
        public override object ExecuteScalar(Query query, IDbTransaction transaction)
        {
            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            #endregion

            object result;
            IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters);

            try
            {
                // do not open connection, we are inside a transaction
                result = command.ExecuteScalar();
            }
            finally
            {
                // do not close connection, we are inside a transaction
            }

            return result;
        }
        
        public override object ExecuteScalar(Query query)
        {

            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            #endregion

            object result;
            IDbConnection conn = GetConnection();
            IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters);

            try
            {
                OpenConnection(conn);
                result = command.ExecuteScalar();
            }
            finally
            {
                CloseConnection(conn);
            }

            return result;
        }
        
        public override int ExecuteNonQuery(Query query)
        {
            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            #endregion

            //Logger.Log(query);
            IDbConnection conn = this.GetConnection();
            IDbCommand command = GetCommand(query.SqlStatement, conn, query.Parameters);

            try
            {
                OpenConnection(conn);
                return command.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection(conn);
            }
        }
        
        public override int ExecuteNonQuery(Query query, IDbTransaction transaction)
        {

            #region Input Validation
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            #endregion

            //Logger.Log(query);
            IDbCommand command = GetCommand(query.SqlStatement, transaction, query.Parameters);
            command.CommandTimeout = 1500;

            //int result;

            try
            {
                // do not try to open connection, we are inside a transaction
                return command.ExecuteNonQuery();
            }
            finally
            {
                // do not close connection, we are inside a transaction
            }
        }       

        #endregion
        
        public override DataTable GetTableData(string tableName)
        {
            return GetTableData(tableName, string.Empty, string.Empty);
        }
        
        public override DataTable GetTableData(string tableName, string columnNames)
        {
            return GetTableData(tableName, columnNames, string.Empty);
        }

        internal List<string> GetDatabaseNameList()
        {
            List<string> databases = new List<string>();
            SqlConnection sqlconn = new SqlConnection(ConnectionString);
            try
            {
                OpenConnection(sqlconn);
                SqlCommand cmd = sqlconn.CreateCommand();
                cmd.CommandText = "sp_databases";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 1500;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    databases.Add(reader.GetString(0));
                }

            }
            finally
            {
                CloseConnection(sqlconn);
            }
            return databases;
        }
        
        public static string BuildDefaultConnectionString(string databaseName)
        {
            return string.Empty;
        }
        /*
        public override DataTable GetCodeTableNamesForProject(Project project)
        {
            List<string> tables = project.GetDataTableList();
            DataSets.TableSchema.TablesDataTable codeTables = project.GetCodeTableList();

            foreach (DataSets.TableSchema.TablesRow row in codeTables)
            {
                tables.Add(row.TABLE_NAME);
            }

            DataView dataView = codeTables.DefaultView;
            return codeTables;
        }
        
        public override Epi.DataSets.TableSchema.TablesDataTable GetCodeTableList(IDbDriver db)
        {
            Epi.DataSets.TableSchema.TablesDataTable table = db.GetTableSchema();

            //remove all databases that do not start with "code"
            DataRow[] rowsFiltered = table.Select("TABLE_NAME not like 'code%'");

            foreach (DataRow rowFiltered in rowsFiltered)
            {
                table.Rows.Remove(rowFiltered);
            }

            return table;
        }
        */
        /// <summary>
        /// Identity MS SQL Server Database
        /// </summary>
        /// <returns>"SQLSERVER"</returns>
        public override string IdentifyDatabase()
        {
            return "SQLSERVER";
        }

        /// <summary>
        /// Inserts the string in escape characters. [] for SQL server and `` for MySQL etc.
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public override string InsertInEscape(string str)
        {
            string newString = string.Empty;
            if (!str.StartsWith("["))
            {
                newString = "[";
            }
            newString += str;
            if (!str.EndsWith("]"))
            {
                newString += "]";
            }
            return newString;
        }

        /// <summary>
        /// Provides a database frieldly string representation of date.
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        //public override string FormatTime(DateTime dt) // TODO: Revisit it.
        //{
        //    return Util.InsertInSingleQuotes(dt.ToString());
        //}
    }
}
